SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [SOI].[FSIC_SOI_TM] AS
BEGIN
DECLARE @FundId VARCHAR(10) = 'FSIC'
 
 DECLARE @ExportId INT =
 (SELECT MAX(e.ExportId)
 FROM    FSFundModel_Valcomm.Reporting.Export e
 WHERE   e.FundId = @FundID )
 
 
 SELECT     id.InstrClass
          , iss.IssuerName, ''
          , soi.GetFootNotes(MAX(a.assetIntrnlId), @ExportId) FootNotes , ''
          , MAX(iss.SubSector)                                Sector, ''
          , soi.GetFormattedRate(MAX(a.AssetIntrnlId), @ExportId ), ''
          
          , NULLIF(CAST(CAST(MAX(i.FLOOR)*100 AS DECIMAL(38,1)) AS VARCHAR(10)) +'%', '0.0%')                                                                                   AS FLOOR, ''
          , CAST(MONTH(MAX(i.MaturityDate)) AS VARCHAR(2)) + '/' + CAST(DAY(MAX(i.MaturityDate)) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(MAX(i.MaturityDate)) AS VARCHAR(4)), 2)    Maturity , '', ''
          ,
            --MAX(CONVERT(VARCHAR(8), i.MaturityDate,1)) Maturity,
            
            CASE
                       WHEN SUM(a.EndAdjAmtFor)> 0
                       THEN SUM(a.EndAdjAmtFor)
                       ELSE
						CASE WHEN MAX(a.FundCreditInstrumentId) IS NOT NULL THEN SUM(fci.TotalCredit) ELSE  SUM(a.EndAdjAmt) END 
            END AS 'Principal Amount', '' 
          , CASE
                       WHEN SUM(a.EndAdjAmtFor) > 0
                       THEN SUM(a.EndAdjAmtFor*a.AmortCostPctPar)
                       ELSE CASE WHEN MAX(a.FundCreditInstrumentId) IS NOT NULL THEN SUM(fci.TotalCredit) ELSE SUM(a.AmortCost) END 
            END AS 'Amortized Cost', ''
          , CASE
                       WHEN SUM(a.MktValFor) > 0
                       THEN SUM(a.MktValFor)
                       ELSE CASE WHEN MAX(a.FundCreditInstrumentId)IS NOT NULL THEN  SUM(fci.TotalCredit)+SUM(fci.Mktval) ELSE SUM(a.MktVal) END 
            END AS 'Fair Value', ''
          , MAX(i.BaseRateType)
          , id.SortOrder
		  , a.FundCreditInstrumentId
		  , i.InstrumentId
 
 
 FROM       FSFundModel_Valcomm.Reporting.Asset a
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Instrument i
            ON         i.ExportId     = a.ExportId
            AND        a.InstrumentId = i.InstrumentId
            
			LEFT JOIN FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci 
			ON fci.ExportId = a.ExportId 
			AND fci.FundCreditInstrumentId = a.FundCreditInstrumentId 
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Issuer iss
            ON         iss.ExportId = a.ExportId
            AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
 
 
 
 WHERE      a.ExportId = @ExportId
 
 
 AND        (a.IsActiveEndOfDay =1 OR a.FundCreditInstrumentId IS NOT NULL)
 GROUP BY  
			a.FundCreditInstrumentId,
			i.InstrumentId,
			 iss.IssuerName
          , id.InstrClass
          , id.SortOrder
 
 
 
 ORDER BY   id.SortOrder
          , iss.IssuerName
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Misc', 'SCHEMA', N'SOI', 'PROCEDURE', N'FSIC_SOI_TM', NULL, NULL
GO
